﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CNotebook.Inventory;
namespace CNotebook.Purchases
{
    public class PurchaseReturnManager:ConnectionManager
    {
        #region Others
        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OGRN WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Add/Update Order
        public void AddPurchaseReturn(PurchaseReturnUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OGRN (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, CardCode, CardName, ContactPerson, Reference, BillTo, ShipTo, Remarks, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, WithHoldingTax, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount) VALUES " +
                                                        " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @CardCode, @CardName, @ContactPerson, @Reference, @BillTo, @ShipTo, @Remarks, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount,  @WithHoldingTax,  @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount)";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));

            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));

            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
            oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
            oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));



            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;

                if (oRow["ExpiryDate"] != DBNull.Value)
                {

                    oCommand.CommandText = "INSERT INTO GRN1 (DocNum, ItemCode, ItemDesc, UoM, LotNumber, ExpiryDate, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @LotNumber, @ExpiryDate, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount)";
                }
                else
                {
                    oCommand.CommandText = "INSERT INTO GRN1 (DocNum, ItemCode, ItemDesc, UoM, LotNumber, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @LotNumber, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount)";
                }


                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                if (oRow["ExpiryDate"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", Convert.ToDateTime(oRow["ExpiryDate"])));
                }

                oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                if (oRow["IsFree"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                }
                else
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                }
                oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                oCommand.ExecuteNonQuery();

            }
        }

        public void UpdatePurchaseReturn(PurchaseReturnUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OGRN SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, Reference=@Reference, BillTo=@BillTo, ShipTo=@ShipTo, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, WithHoldingTax=@WithHoldingTax, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount" +
                                                        " WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));

            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));

            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));

            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE GRN1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {

                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO GRN1 (DocNum, ItemCode, ItemDesc, UoM, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalDiscount, Amount) VALUES " +
                                                                "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalDiscount, @Amount)";


                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));

                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));

                    oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", oRow["ExpiryDate"]));

                    if (oRow["Qty"] == DBNull.Value)
                    {
                        int _ZeroValue = 0;
                        oRow["Qty"] = _ZeroValue;
                    }
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oRow["TotalDiscount"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                    oCommand.ExecuteNonQuery();

                }

            }
        }
        #endregion

        #region Post/Cancel/Close Order
        public void PostPurchaseReturn(string _WAREHOUSE, string _DOCNUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OGRN SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.ExecuteNonQuery();


            string selectSQL = "SELECT * FROM GRN1 WHERE DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);


            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                StockManager oManager = new StockManager();
                oManager.Connection = this.Connection;

                if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                {
                    oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), (Convert.ToDouble(oRow["Qty"]) * -1).ToString());
                }
                else
                {
                    WarehouseManager oWhs = new WarehouseManager();
                    oWhs.ConnectionString = this.ConnectionString;
                    oWhs.Open();
                    DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                    oWhs.Close();
                    oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemDesc"].ToString(), (Convert.ToDouble(oRow["Qty"]) * -1).ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(),  oRow["ExpiryDate"].ToString());
                }
            }
        }

        public void CancelPurchaseReturn(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OGRN SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }

        public void ClosePurchaseReturn(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OGRN SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Getting Data

        
        public DataTable GetPurchaseReturnBySupplier(string _CardName)
        {
            _CardName = '%' + _CardName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OGRN WHERE CardName LIKE @CardName ORDER BY POSTINGDATE DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPurchaseReturnDocuments(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OGRN WHERE DocNum LIKE @DOCNUM ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPostedPurchaseReturns(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OGRN WHERE DocNum LIKE @DOCNUM and DocStatus='Posted' ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataRow GetPurchaseReturn(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OGRN WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetPurchaseReturn(string _CardCode, string _DocNumber, string _DocumentStatus)
        {
            _DocNumber = '%' + _DocNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM OGRN WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND DocStatus=@DocStatus ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public string GetPurchaseReturnNumber(string _Cardcode, string _DocNumber, string _DocumentStatus)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DocNum FROM OGRN WHERE CardCode=@CardCode AND DocNum=@DOCNUM AND DocStatus=@DocStatus";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _Cardcode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            if (ds.Tables[0].Rows.Count == 0)
            {
                return "";
            }
            else
            {
                DataRow oRow = ds.Tables[0].Rows[0];
                return oRow["DocNum"].ToString();
            }

        }

        public DataTable GetPurchaseReturnLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM GRN1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        #endregion

        #region Report Retreivable


        //------------------------------------------------------------------
        //  HEADER

        public DataTable GetPurchaseReturn(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_GRT";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetReturnStatus(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_PURRET_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion
    }
}
